![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Chapter 27
|
Option | Description |
---|---|
CHOOSE | This option allows the Oracle optimizer to choose an optimization mode based on the availability of statistics on a particular table, cluster, or index. If statistics are available for any of the tables accessed in the SQL statement, the cost-based approach is used with the goal of best throughput. If none of the tables has statistics available, the rule-based approach is used; the rule-based approach is also the default if no optimization approach is specified. |
RULE | This option causes the Oracle optimizer to always use the rule-based optimization approach, regardless of any statistics that may have been gathered for the tables being accessed. |
ALL_ROWS | This option causes the optimizer to used the cost-based approach on all SQL statements, even if there are no statistics available for the tables being accessed. This approach has the goal of best throughput, with the least amount of system resources being used. |
FIRST_ROWS | This option causes the optimizer to use the cost-based approach on all SQL statements, even if no statistics are available for the tables being accessed. This approach has the goal of best response time. |
NOTE: If the cost-based optimization approach is used and no internal statistics are available for a table that is being accessed, other information (such as the number of data blocks in the table) is used to estimate the cost of various operations.
In general, the cost-based approach is the recommended approach. In most cases, the cost-based approach determines an execution plan that is as good or better than the rule-based approach. However, if you have manually tuned your SQL statements, you may get better performance with rule-based optimization than cost-based optimization.
The rule-based approach can be useful if you are moving a highly tuned application from an older version of Oracle that has been using the rule-based approach. This and a lack of statistics may cause rule-based optimization to be more efficient than the cost-based approach. However, as you gather statistics on your database, you may want to migrate to cost-based optimization.
The following sections examine both approaches. Hints can also be very useful in optimizing the execution of your SQL statements; hints are discussed in Chapter 30, Using Hints.
Previous | Table of Contents | Next |
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |